using System;
using System.Text;
using ApexSql.Code.SqlServer;

namespace cgu
{
	public static class ActiveTableUtil
	{
		public enum ParamTypeEnum
		{
			InsertIdentityOut,
			AssignInsertIdentityOut,
			CommonInsertUpdate,
			Delete,
			SelectByPrimaryKeys,
			SelectByForeignKeys,
			Update,
			SelectOut
		}

		//---------------------------------------------
		#region private member vars and public accessors and methods
		//---------------------------------------------

		private static string _ClassName;
		public static string ClassName
		{
			get { return _ClassName; }
		}

		private static string _TableName;
		public static string TableName
		{
			get { return _TableName; }
		}

		private static string _InnerDataClassName;
		public static string InnerDataClassName
		{
			get { return _InnerDataClassName; }
		}

		private static Table _ActiveTable;
		public static Table ActiveTable
		{
			get { return _ActiveTable; }
		}

		private static string _CSharpDalFunctionParamListPk;
		public static string CSharpDalFunctionParamListPk
		{
			get { return _CSharpDalFunctionParamListPk; }
		}

		private static string _CSharpDalFunctionParamListOut;
		public static string CSharpDalFunctionParamListOut
		{
			get { return _CSharpDalFunctionParamListOut; }
		}

		private static string _EnumeratedColumnList;
		public static string EnumeratedColumnList
		{
			get { return _EnumeratedColumnList; }
		}

		private static string _EnumeratedForeignKeyColumnList;
		public static string EnumeratedForeignKeyColumnList
		{
			get { return _EnumeratedForeignKeyColumnList; }
		}

		private static string _EntityDataClassMemberDeclarations;
		public static string EntityDataClassMemberDeclarations
		{
			get { return _EntityDataClassMemberDeclarations; }
		}

		private static string _EntityRefreshWithDataRow;
		public static string EntityRefreshWithDataRow
		{
			get { return _EntityRefreshWithDataRow; }
		}

		private static string _EntityRefreshWithReader;
		public static string EntityRefreshWithReader
		{
			get { return _EntityRefreshWithReader; }
		}

		private static string _ClassDataToString;
		public static string ClassDataToString
		{
			get { return _ClassDataToString; }
		}

		private static string _TopLevelNameSpaceForGeneratedFiles;
		public static string TopLevelNameSpaceForGeneratedFilesForGeneratedFiles
		{
			get { return _TopLevelNameSpaceForGeneratedFiles; }
		}

		private static string _LibraryNameSpace;
		public static string LibraryNameSpace
		{
			get { return _LibraryNameSpace; }
		}

		private static bool _ExcludeTempFields;
		public static bool ExcludeTempFields
		{
			get { return _ExcludeTempFields; }
		}

		private static bool _DateStampFieldsAreReadOnly;
		public static bool DateStampFieldsAreReadOnly
		{
			get { return _DateStampFieldsAreReadOnly; }
		}

		private static int _DotNetFrameworkTarget;
		public static int DotNetFrameworkTarget
		{
			get { return _DotNetFrameworkTarget; }
		}

		/// <summary>
		/// 'Initializes' the current table.  
		///	The idea is to call helper functions just once per table and to limit the
		/// number of visible objects available to the caller (hide all of those helper functions).
		/// </summary>
		public static void TableInit(Table objTable, bool bExcludeTempFields, bool bDateStampFieldsAreReadOnly, string sTopLevelNameSpaceForGeneratedFiles, string sLibraryNameSpace, int iDotNetFrameworkTarget)
		{
			_ActiveTable = objTable;
			_ExcludeTempFields = bExcludeTempFields;
			_DateStampFieldsAreReadOnly = bDateStampFieldsAreReadOnly;
			_DotNetFrameworkTarget = iDotNetFrameworkTarget; // 20 = 2.0, 35 = 3.5, etc

			_TopLevelNameSpaceForGeneratedFiles = sTopLevelNameSpaceForGeneratedFiles;
			_LibraryNameSpace = sLibraryNameSpace;
			_TableName = objTable.Name;
			_ClassName = cgu.CommonUtil.GetTableClassName(objTable);
			_InnerDataClassName = cgu.CommonUtil.GetTableClassName(objTable) + "_InnerData";
			_ClassDataToString = GetClassDataToString(_ActiveTable, bExcludeTempFields);

			_CSharpDalFunctionParamListOut = GetCSharpDalFunctionParamListOut(_ActiveTable, bExcludeTempFields);
			_CSharpDalFunctionParamListPk = GetCSharpDalFunctionParamListPk(_ActiveTable);
			_EnumeratedColumnList = GetEntityEnumList(_ActiveTable, 3, bExcludeTempFields);
			_EnumeratedForeignKeyColumnList = GetForeignKeyEntityEnumList(_ActiveTable, 3);

			_EntityDataClassMemberDeclarations = GetEntityDataClassMemberDeclarations(_ActiveTable, bExcludeTempFields);
			_EntityRefreshWithDataRow = GetEntityRefreshWithDataRow(_ActiveTable, bExcludeTempFields);
			_EntityRefreshWithReader = GetEntityRefreshWithReader(_ActiveTable, bExcludeTempFields);

			return;
		}

		/// <summary>
		/// ADO Library function calls to add proc parameters for table
		/// </summary>
		/// <returns></returns>
		public static string AdoParameterFunctionListForTable(ParamTypeEnum enmParamType, int iIndentLevel)
		{
			return GetAdoParamFunctionList(enmParamType, ActiveTable, _ExcludeTempFields, _DateStampFieldsAreReadOnly, iIndentLevel, _LibraryNameSpace);
		}

		/// <summary>
		/// ADO Library function calls to add proc parameters for a foreign key
		/// </summary>
		/// <returns></returns>
		public static string AdoParameterFunctionListForForeignKey(Key fKey, int iIndentLevel)
		{
			return GetAdoParamFunctionListForForeignKey(fKey, _ExcludeTempFields, iIndentLevel, _LibraryNameSpace);
		}

		/// <summary>
		/// ADO Library function calls to add proc parameters for an Index
		/// </summary>
		/// <returns></returns>
		public static string AdoParameterFunctionListForIndex(Index idx, int iIndentLevel)
		{
			return GetAdoParamFunctionListForIndex(idx, _ExcludeTempFields, iIndentLevel, _LibraryNameSpace);
		}

		/// <summary>
		/// Specialized list for Entity template: "innerData" property assignments
		/// </summary>
		/// <param name="bPrefixPropertyNames">Prefix property names with type indicator (Hungarian notation)</param>
		/// <param name="bIncludePrimaryKeys">Include Primary Keys</param>
		/// <returns></returns>
		public static string ParamsToInitializerList(bool bPrefixPropertyNames, bool bIncludePrimaryKeys)
		{
			return GetParamsToInitializerList(_ActiveTable, _ExcludeTempFields, _DateStampFieldsAreReadOnly,
			                                    bPrefixPropertyNames, bIncludePrimaryKeys, 3);
		}

		/// <summary>
		/// Gets comma delimited list of parameters
		/// </summary>
		/// <param name="bPrefixPropertyNames">Prefix property names with type indicator (Hungarian notation)</param>
		/// <param name="bIncludeCSharpTypeDeclaration">Include leading type declaration for the property (String, Int32, etc)</param>
		/// <param name="bIncludeParmaryKeys">Include Primary Keys</param>
		/// <returns></returns>
		public static string CommaDelimitedParamList(bool bPrefixPropertyNames, bool bIncludeCSharpTypeDeclaration, bool bIncludeParmaryKeys)
		{
			return GetCommaDelimitedParamList(_ActiveTable, _ExcludeTempFields, _DateStampFieldsAreReadOnly, bPrefixPropertyNames, bIncludeCSharpTypeDeclaration, bIncludeParmaryKeys);
		}

		// ************************************************************************************
		#endregion private member vars and public accessors and methods
		// ************************************************************************************

		// ************************************************************************************
		#region table-related private 'helper' procs
		// ************************************************************************************


		/// <summary>
		/// Formats enum declarations for the entity class
		/// </summary>
		/// <param name="objTable"></param>
		/// <param name="iTabIndex"></param>
		/// <param name="bExcludeTempFields">Optionally exclude fields ending with 'temp'</param>
		/// <returns></returns>
		private static string GetEntityEnumList(Table objTable, int iTabIndex, bool bExcludeTempFields)
		{
			StringBuilder sBuilder = new StringBuilder("\r\n");
			int iColNbr = 0;

			for (int x = 0; x < objTable.Columns.Count; x++)
			{
				if (!cgu.CommonUtil.ColumnExcludeCheck(objTable.Columns[x].Name, bExcludeTempFields))
				{
					iColNbr++;

					sBuilder.Append('\t', iTabIndex);
					sBuilder.Append(FormatEnumAttribute(objTable.Columns[x]));
					sBuilder.Append('\t', iTabIndex);
					sBuilder.Append(cgu.CommonUtil.GetColumnPropertyName(objTable.Columns[x]) + "=" + (iColNbr - 1).ToString());
					sBuilder.Append(",\r\n");
				}
			}

			//remove trailing comma and newline and return
			if (sBuilder.Length > 1)
			{
				sBuilder = sBuilder.Remove(sBuilder.Length - 3, 3);
			}

			return sBuilder.ToString();
		}

		/// <summary>
		/// Formats enum declaration list for foreign key values only
		/// </summary>
		/// <param name="objTable"></param>
		/// <param name="iTabIndex"></param>
		/// <returns></returns>
		private static string GetForeignKeyEntityEnumList(Table objTable, int iTabIndex)
		{
			StringBuilder sBuilder = new StringBuilder("\r\n");
			int iColNbr = 0;

			foreach (Key fk in objTable.ForeignKeys)
			{
				//only fetch single keys
				if (fk.KeyColumns.Count == 1)
				{
					iColNbr++;

					sBuilder.Append('\t', iTabIndex);
					sBuilder.Append(FormatEnumAttribute(fk.KeyColumns[0]));
					sBuilder.Append('\t', iTabIndex);
					sBuilder.Append(cgu.CommonUtil.GetColumnPropertyName(fk.KeyColumns[0]) + "=" + (iColNbr - 1).ToString());
					sBuilder.Append(",\r\n");
				}
			}

			//remove trailing comma and newline and return
			if (sBuilder.Length > 3)
			{
				sBuilder = sBuilder.Remove(sBuilder.Length - 3, 3);
			}

			return sBuilder.ToString();
		}

		/// <summary>
		/// Formats enum attribute decoration text
		/// </summary>
		/// <param name="objColumn"></param>
		/// <returns></returns>
		private static string FormatEnumAttribute(Column objColumn)
		{
			StringBuilder sBuilder = new StringBuilder();
			sBuilder.AppendFormat("[{0}.DatabaseColumnAttribute(\"" + objColumn.Name + "\",\"" + objColumn.SQLDatatype + "\"," +
														objColumn.IsInPrimaryKey.ToString().ToLower() + "," + objColumn.IsIdentity.ToString().ToLower() + 
														"," + objColumn.IsInForeignKey.ToString().ToLower() + ")]\r\n", _LibraryNameSpace);

			return sBuilder.ToString();
		}

		/// <summary>
		/// Builds CSharp function parameter list for primary keys of given table
		/// </summary>
		/// <param name="objTable"></param>
		/// <returns></returns>
		private static string GetCSharpDalFunctionParamListPk(Table objTable)
		{
			StringBuilder sBuilder = new StringBuilder();

			foreach (Column objColumn in objTable.PrimaryKeyColumns)
			{
				sBuilder.AppendFormat("{0} {1}, ", objColumn.AdoNetMapping.CLRType, cgu.CommonUtil.GetColumnPropertyName(objColumn));
			}

			//remove final ", " from string (if any keys were found)
			if (sBuilder.Length > 2)
			{
				sBuilder.Remove(sBuilder.Length - 2, 2);
			}

			return sBuilder.ToString();
		}

		/// <summary>
		/// Builds CSharp output function parameter all 'output' fields
		/// </summary>
		/// <param name="objTable"></param>
		/// <param name="bExcludeTempFields">Optionally exclude fields ending with 'temp'</param>
		/// <returns></returns>
		private static string GetCSharpDalFunctionParamListOut(Table objTable, bool bExcludeTempFields)
		{
			StringBuilder sBuilder = new StringBuilder();

			foreach (Column objColumn in objTable.Columns)
			{
				if ((!objColumn.IsInPrimaryKey) && !cgu.CommonUtil.ColumnExcludeCheck(objColumn.Name, bExcludeTempFields))
				{
					sBuilder.AppendFormat("out {0} {1}, ", objColumn.AdoNetMapping.CLRType, cgu.CommonUtil.GetColumnPropertyName(objColumn));
				}
			}

			//remove final ", " from string (if any keys were found)
			if (sBuilder.Length > 2)
			{
				sBuilder.Remove(sBuilder.Length - 2, 2);
			}

			//retur params OUT list
			return sBuilder.ToString();
		}

		/// <summary>
		/// Builds CSharp function parameter list for given foreign key 
		/// </summary>
		/// <param name="fk">(foreign) Key object </param>
		/// <returns></returns>
		public static string GetForeignKeyCSharpFunctionParamList(Key fk)
		{
			StringBuilder sBuilder = new StringBuilder();

			foreach (Column objColumn in fk.KeyColumns)
			{
				sBuilder.AppendFormat("{0} {1}, ", objColumn.AdoNetMapping.CLRType, cgu.CommonUtil.GetColumnPropertyName(objColumn));
			}

			//remove final ", " from string (if any keys were found)
			if (sBuilder.Length > 2)
			{
				sBuilder.Remove(sBuilder.Length - 2, 2);
			}

			return sBuilder.ToString();
		}

		/// <summary>
		/// Builds CSharp function parameter list for given Index
		/// </summary>
		/// <param name="idx">(foreign) Key object </param>
		/// <returns></returns>
		public static string GetIndexCSharpFunctionParamList(Index idx)
		{
			StringBuilder sBuilder = new StringBuilder();

			foreach (Column objColumn in idx.IndexedColumns)
			{
				sBuilder.AppendFormat("{0} {1}, ", objColumn.AdoNetMapping.CLRType, cgu.CommonUtil.GetColumnPropertyName(objColumn));
			}

			//remove final ", " from string (if any keys were found)
			if (sBuilder.Length > 2)
			{
				sBuilder.Remove(sBuilder.Length - 2, 2);
			}

			return sBuilder.ToString();
		}

		/// <summary>
		/// Builds CSharp function parameter list for given foreign key 
		/// </summary>
		/// <param name="fk">(foreign) Key object </param>
		/// <returns></returns>
		public static string GetForeignKeyCSharpFunctionParamListPass(Key fk)
		{
			StringBuilder sBuilder = new StringBuilder();

			foreach (Column objColumn in fk.KeyColumns)
			{
				sBuilder.AppendFormat("{0}, ", cgu.CommonUtil.GetColumnPropertyName(objColumn));
			}

			//remove final ", " from string (if any keys were found)
			if (sBuilder.Length > 2)
			{
				sBuilder.Remove(sBuilder.Length - 2, 2);
			}

			return sBuilder.ToString();
		}

		/// <summary>
		/// Builds CSharp function parameter list for given Index
		/// </summary>
		/// <param name="idx">Index object </param>
		/// <returns></returns>
		public static string GetForeignKeyCSharpFunctionParamListPass(Index idx)
		{
			StringBuilder sBuilder = new StringBuilder();

			foreach (Column objColumn in idx.IndexedColumns)
			{
				sBuilder.AppendFormat("{0}, ", cgu.CommonUtil.GetColumnPropertyName(objColumn));
			}

			//remove final ", " from string (if any keys were found)
			if (sBuilder.Length > 2)
			{
				sBuilder.Remove(sBuilder.Length - 2, 2);
			}

			return sBuilder.ToString();
		}

		/// <summary>
		/// Specialized list for Entity template: "innerData" property assignments
		/// </summary>
		/// <param name="objTable"></param>
		/// <param name="bExcludeTempFields">Optionally exclude fields ending with 'temp'</param>
		/// <param name="bDateStampFieldsAreReadOnly">Optionally excludes read-only date stamp fields</param>
		/// <param name="bPrefixPropertyNames">Prefix property names with type indicator (Hungarian notation)</param>
		/// <param name="bIncludePrimaryKeys">Include Primary Keys</param>
		/// <param name="iTabIndent"></param>
		/// <returns></returns>
		private static string GetParamsToInitializerList(Table objTable, bool bExcludeTempFields, bool bDateStampFieldsAreReadOnly, bool bPrefixPropertyNames, 
																												bool bIncludePrimaryKeys, int iTabIndent)
		{
			StringBuilder sBuilder = new StringBuilder();

			//use initializer list if using .net 3.0 or greater
			if (_DotNetFrameworkTarget >= 30)
			{
				//begin initializer {
				sBuilder.Append("\r\n");
				sBuilder.Append('\t', iTabIndent + 5);
				sBuilder.Append("{\r\n");
			}
			else
			{
				//just terminate the class instaniation
				sBuilder.Append(";\r\n\r\n");
			}

			//first fetch primary keys
			if (bIncludePrimaryKeys)
			{
				foreach (Column objColumn in objTable.PrimaryKeyColumns)
				{
					if (!cgu.CommonUtil.ColumnExcludeCheck(objColumn.Name, bExcludeTempFields))
					{
						sBuilder.Append(FormatPropertyInitializer(objColumn, bPrefixPropertyNames, iTabIndent));
					}
				}
			}

			//followed by non-primaries
			foreach (Column objColumn in objTable.Columns)
			{
				if (!objColumn.IsInPrimaryKey &&
						(!cgu.CommonUtil.ColumnExcludeCheck(objColumn.Name, bExcludeTempFields) && 
						(!cgu.CommonUtil.ColumnIsAutoFilled(objColumn, bDateStampFieldsAreReadOnly)))
					)
				{
					sBuilder.Append(FormatPropertyInitializer(objColumn, bPrefixPropertyNames, iTabIndent));
				}
			}

			//remove trailing formatting chars
			if (sBuilder.Length > 0)
			{
				sBuilder.Remove(sBuilder.Length - 3, 3);
			}

			//terminiate the list if >= 30
			if (_DotNetFrameworkTarget >= 30)
			{
				//begin initializer {
				sBuilder.Append("\r\n");
				sBuilder.Append('\t', iTabIndent + 5);
				sBuilder.Append("};\r\n");
			}
			else
			{
				//just add a newline
				sBuilder.Append("\r\n");
			}

			return sBuilder.ToString();
		}

		/// <summary>
		/// Formats initilizer for target framework
		/// </summary>
		private static string FormatPropertyInitializer(Column objColumn, bool bPrefixPropertyNames, int iTabIndent)
		{
			StringBuilder sBuilder = new StringBuilder(); 

			//format the property name
			string sPropertyName = (bPrefixPropertyNames)
															? cgu.CommonUtil.GetColumnPrefixedPropertyName(objColumn)
															: cgu.CommonUtil.GetColumnPropertyName(objColumn);
			
			//use 'initializer' format if targeting 3.0 or higher
			if (_DotNetFrameworkTarget >= 30)
			{
				sBuilder.Append('\t', iTabIndent + 6);
				sBuilder.AppendFormat("{0} = {1},", cgu.CommonUtil.GetColumnPropertyName(objColumn), sPropertyName);
			}
			else
			{
				//otherwise 'Class.Property = Param' format. Leave extra space behind {1} so that the caller's .remove works correctly.
				sBuilder.Append('\t', iTabIndent);
				sBuilder.AppendFormat("innerData.{0} = {1}; ", cgu.CommonUtil.GetColumnPropertyName(objColumn), sPropertyName);
			}

			//add newline and return)
			sBuilder.Append("\r\n");
			return sBuilder.ToString();
		}

		/// <summary>
		/// Specialized list for Entity template for passing parameters to another function
		/// </summary>
		/// <param name="objTable"></param>
		/// <param name="bExcludeTempFields">Optionally exclude fields ending with 'temp'</param>
		/// <param name="bDateStampFieldsAreReadOnly">Optionally excludes read-only date stamp fields</param>
		/// <param name="bPrefixPropertyNames">Prefix property names with type indicator (Hungarian notation)</param>
		/// <param name="bIncludeCSharpTypeDeclaration">Include leading type declaration for the property (String, Int32, etc)</param>
		/// <param name="bIncludePrimaryKeys">Include Primary Keys</param>
		/// <returns></returns>
		private static string GetCommaDelimitedParamList(Table objTable, bool bExcludeTempFields, bool bDateStampFieldsAreReadOnly, bool bPrefixPropertyNames, bool bIncludeCSharpTypeDeclaration, bool bIncludePrimaryKeys)
		{
			StringBuilder sBuilder = new StringBuilder();

			//first fetch primary keys
			if (bIncludePrimaryKeys)
			{
				foreach (Column objColumn in objTable.PrimaryKeyColumns)
				{
					if (!cgu.CommonUtil.ColumnExcludeCheck(objColumn.Name, bExcludeTempFields))
					{
						//get type and property names
						string sCSharpTypeName = (bIncludeCSharpTypeDeclaration)
						                         	? cgu.CommonUtil.GetNullableColumnTypeDef(objColumn) + " "
						                         	: "";
						string sPropertyName = (bPrefixPropertyNames)
						                       	? cgu.CommonUtil.GetColumnPrefixedPropertyName(objColumn)
						                       	: cgu.CommonUtil.GetColumnPropertyName(objColumn);

						sBuilder.AppendFormat("{0}{1}, ", sCSharpTypeName, sPropertyName);
					}
				}
			}

			//followed by non-primaries
			foreach (Column objColumn in objTable.Columns)
			{
				if (!objColumn.IsInPrimaryKey &&
						(!cgu.CommonUtil.ColumnExcludeCheck(objColumn.Name, bExcludeTempFields) &&
						(!cgu.CommonUtil.ColumnIsAutoFilled(objColumn, bDateStampFieldsAreReadOnly)))
					)
				{
					//get type and property names
					string sCSharpTypeName = (bIncludeCSharpTypeDeclaration) ? cgu.CommonUtil.GetNullableColumnTypeDef(objColumn) + " " : "";
					string sPropertyName = (bPrefixPropertyNames) ? cgu.CommonUtil.GetColumnPrefixedPropertyName(objColumn) : cgu.CommonUtil.GetColumnPropertyName(objColumn);

					sBuilder.AppendFormat("{0}{1}, ", sCSharpTypeName, sPropertyName);
				}
			}

			//remove trailing formatting chars
			if (sBuilder.Length > 0)
			{
				return sBuilder.ToString().Substring(0, sBuilder.Length - 2);
			}

			return String.Empty;
		}

		/// <summary>
		/// Specialized list for Entity template: ToString() override for class values
		/// </summary>
		/// <param name="objTable"></param>
		/// <param name="bExcludeTempFields"></param>
		/// <returns></returns>
		private static string GetClassDataToString(Table objTable, bool bExcludeTempFields)
		{
			//calculate column count
			int iNonExcludedColumns = objTable.Columns.Count - GetExcludedColumnCount(objTable, bExcludeTempFields);

			//initialize formatting portion with type info
			StringBuilder sBuilder = new StringBuilder();
			sBuilder.Append("string.Format(System.Globalization.CultureInfo.InvariantCulture,\r\n\t\t\t\t\"{" + (iNonExcludedColumns + 1).ToString() + "}{" + iNonExcludedColumns.ToString() + "}");

			//build string.format formatting string
			int iColNbr = 0;
			for (int x = 0; x < objTable.Columns.Count; x++)
			{
				Column objColumn = objTable.Columns[x];
				if (!cgu.CommonUtil.ColumnExcludeCheck(objColumn.Name, bExcludeTempFields))
				{
					string sTemp = "~ " + cgu.CommonUtil.GetColumnPropertyName(objColumn) + ": {" + iColNbr.ToString() + "}{" + iNonExcludedColumns.ToString() + "}";
					sBuilder.Append(sTemp);
					iColNbr++;
				}
			}
			sBuilder.Append("\",\r\n\r\n");

			//add formatted property values
			foreach (Column objColumn in objTable.Columns)
			{
				if (!cgu.CommonUtil.ColumnExcludeCheck(objColumn.Name, bExcludeTempFields))
				{
					string sTemp;

					//if nullable, take care to return 'null' if null
					if (objColumn.IsNullable)
					{
						sTemp = "\t\t\t\t(this." + cgu.CommonUtil.GetColumnPropertyName(objColumn) + " == null) ? \"null\" : this." + cgu.CommonUtil.GetColumnPropertyName(objColumn) +
										".ToString(),\r\n";
					}
					else
					{
						sTemp = "\t\t\t\tthis." + cgu.CommonUtil.GetColumnPropertyName(objColumn) + ",\r\n";
					}

					//add row to string
					sBuilder.Append(sTemp);
				}
			}

			//add closing formating
			sBuilder.Append("\r\n\t\t\t\tEnvironment.NewLine,\r\n\t\t\t\tthis.GetType());\r\n");

			//return formatted tostring template
			return sBuilder.ToString();
		}

		/// <summary>
		/// Specialized list for Entity template: returns formatted property defs from column names.
		/// </summary>
		/// <param name="objTable"></param>
		/// <param name="bExcludeTempFields">Optionally exclude columns ending with "Temp"</param>
		/// <returns></returns>
		private static string GetEntityDataClassMemberDeclarations(Table objTable, bool bExcludeTempFields)
		{
			StringBuilder sBuilder = new StringBuilder();

			foreach (Column objColumn in objTable.Columns)
			{
				if (!cgu.CommonUtil.ColumnExcludeCheck(objColumn.Name, bExcludeTempFields))
				{
					sBuilder.Append(FormatCSharpEntityDataClassMemberDeclaration(objColumn));
				}
			}

			return sBuilder.Remove(sBuilder.Length - 2, 2).ToString();
		}

		/// <summary>
		///  Specialized list for Entity template: called by GetEntityClassMemberDeclarations
		/// </summary>
		/// <param name="objColumn"></param>
		/// <returns></returns>
		private static string FormatCSharpEntityDataClassMemberDeclaration(Column objColumn)
		{
			string sInitializer;

			//set deftault value
			if ((objColumn.SQLDefault != null) && (objColumn.SQLDefault.TextBody != ""))
			{
				sInitializer = ";"; // "=" + GetColumnDefaultValue(objColumn) + ";";
			}
			else
			{
				sInitializer = ";";
			}

			//set nullable
			string sClrType = cgu.CommonUtil.GetNullableColumnTypeDef(objColumn);

			//public System.String Modified_user = string.Empty;
			return string.Format("public {0} {1}{2}\r\n\t\t", sClrType, cgu.CommonUtil.GetColumnPropertyName(objColumn.Name), sInitializer);
		}

		/// <summary>
		/// Helps build ADO parameter definition list for the DAL 
		/// </summary>
		/// <param name="enmParamType"></param>
		/// <param name="objTable"></param>
		/// <param name="bExcludeTempFields">Optionally exlclude columns ending with 'temp'</param>
		/// <param name="bDateStampFieldsAreReadOnly">Optionally make specific date/time stamp read-only</param>
		/// <param name="iIndentLevel">Indentation Level</param>
		/// <param name="sTopLevelNameSpaceForGeneratedFiles">Top level namespace name</param>
		/// <returns></returns>
		private static string GetAdoParamFunctionList(ParamTypeEnum enmParamType, Table objTable, bool bExcludeTempFields, bool bDateStampFieldsAreReadOnly, int iIndentLevel, string sTopLevelNameSpaceForGeneratedFiles)
		{
			StringBuilder sBuilder = new StringBuilder();

			//add non-primary key params (depending on the type)
			switch (enmParamType)
			{
				case ParamTypeEnum.Update:
					//add primary key identity columns that were not included in CommonInsertUpdate
					foreach (Column col in objTable.PrimaryKeyColumns)
					{
						if (col.IsIdentity)
						{
							sBuilder.Append(CommonUtil.GetAdoParameterFunctionCallForColumn(col, CommonUtil.enmAdoParamValueSource.enmEntityType, iIndentLevel, sTopLevelNameSpaceForGeneratedFiles, System.Data.ParameterDirection.Input));
						}
					}
					break;
				case ParamTypeEnum.InsertIdentityOut:	
					//add identity columns
					foreach (Column col in objTable.Columns)
					{
						if (col.IsIdentity)
						{
							sBuilder.Append(CommonUtil.GetAdoParameterFunctionCallForColumn(col, CommonUtil.enmAdoParamValueSource.enmOutputParam, iIndentLevel, sTopLevelNameSpaceForGeneratedFiles, System.Data.ParameterDirection.Output));
						}
					}
					break;
				case ParamTypeEnum.AssignInsertIdentityOut:
					foreach (Column col in objTable.Columns)
					{
						if (col.IsIdentity)
						{
							sBuilder.AppendFormat("entity.{0} = ({1})(cmd.Parameters[\"@out_{0}\"].Value);\r\n", cgu.CommonUtil.GetColumnPropertyName(col), cgu.CommonUtil.GetCSharpType(col));
							sBuilder.Append('\t', iIndentLevel);
						}
					}
					break;
				case ParamTypeEnum.SelectOut:
					foreach (Column col in objTable.Columns)
					{
						if (!CommonUtil.ColumnExcludeCheck(col.Name, bExcludeTempFields) && !col.IsInPrimaryKey)
						{
							sBuilder.AppendFormat("{0} = ({1})(cmd.Parameters[\"@out_{0}\"].Value);\r\n", cgu.CommonUtil.GetColumnPropertyName(col), cgu.CommonUtil.GetCSharpType(col));
							sBuilder.Append('\t', iIndentLevel);
						}
					}
					break;
				case ParamTypeEnum.CommonInsertUpdate:
					//add params common to insert and update operations
					foreach (Column col in objTable.Columns)
					{
						//special handling of "ModifiedUser" and "InsertUser" columns (add param regardless of readonly status and set to current user)
						if (col.Name == "ModifiedUser" || col.Name == "InsertUser")
						{
							sBuilder.Append(cgu.CommonUtil.GetAdoParameterFunctionCallForColumn(col, CommonUtil.enmAdoParamValueSource.enmCurrentUser,
								                                                                    iIndentLevel, sTopLevelNameSpaceForGeneratedFiles, System.Data.ParameterDirection.Input));
						}
						else
						{
							if (
									(!CommonUtil.ColumnExcludeCheck(col.Name, bExcludeTempFields)) &&
							     (!CommonUtil.ColumnIsAutoFilled(col, bDateStampFieldsAreReadOnly)))
							{
								sBuilder.Append(cgu.CommonUtil.GetAdoParameterFunctionCallForColumn(col, CommonUtil.enmAdoParamValueSource.enmEntityType,
																																										iIndentLevel, sTopLevelNameSpaceForGeneratedFiles, System.Data.ParameterDirection.Input));
							}
						}
					}
					break;
				case ParamTypeEnum.Delete:
				case ParamTypeEnum.SelectByPrimaryKeys:
					//add primary key ado param defs
					foreach (Column col in objTable.PrimaryKeyColumns)
					{
						sBuilder.Append(CommonUtil.GetAdoParameterFunctionCallForColumn(col, CommonUtil.enmAdoParamValueSource.enmScalarType, iIndentLevel, sTopLevelNameSpaceForGeneratedFiles, System.Data.ParameterDirection.Input));
					}

					break;
				default:
					throw new Exception(enmParamType.ToString() + " is not a recognized group type.");
			}

			//return def
			return sBuilder.ToString();
		}


		/// <summary>
		/// Helps build ADO parameter definition list for the DAL 
		/// </summary>
		/// <param name="fKey">foreign Key object</param>
		/// <param name="bExcludeTempFields">Optionally exlclude columns ending with 'temp'</param>
		/// <param name="iIndentLevel">Indentation Level</param>
		/// <param name="sTopLevelNameSpaceForGeneratedFiles">Top level namespace name</param>
		/// <returns></returns>
		private static string GetAdoParamFunctionListForForeignKey(Key fKey, bool bExcludeTempFields, int iIndentLevel, string sTopLevelNameSpaceForGeneratedFiles)
		{
			StringBuilder sBuilder = new StringBuilder();

			foreach (Column objColumn in fKey.KeyColumns)
			{
				if (!CommonUtil.ColumnExcludeCheck(objColumn.Name, bExcludeTempFields))
				{
					sBuilder.Append(cgu.CommonUtil.GetAdoParameterFunctionCallForColumn(objColumn, CommonUtil.enmAdoParamValueSource.enmScalarType, iIndentLevel, sTopLevelNameSpaceForGeneratedFiles, System.Data.ParameterDirection.Input));
				}
			}

			return sBuilder.ToString();
		}

		/// <summary>
		/// Helps build ADO parameter definition list for the DAL 
		/// </summary>
		/// <param name="idx">Index object</param>
		/// <param name="bExcludeTempFields">Optionally exlclude columns ending with 'temp'</param>
		/// <param name="iIndentLevel">Indentation Level</param>
		/// <param name="sTopLevelNameSpaceForGeneratedFiles">Top level namespace name</param>
		/// <returns></returns>
		private static string GetAdoParamFunctionListForIndex(Index idx, bool bExcludeTempFields, int iIndentLevel, string sTopLevelNameSpaceForGeneratedFiles)
		{
			StringBuilder sBuilder = new StringBuilder();

			foreach (Column objColumn in idx.IndexedColumns)
			{
				if (!CommonUtil.ColumnExcludeCheck(objColumn.Name, bExcludeTempFields))
				{
					sBuilder.Append(cgu.CommonUtil.GetAdoParameterFunctionCallForColumn(objColumn, CommonUtil.enmAdoParamValueSource.enmScalarType, iIndentLevel, sTopLevelNameSpaceForGeneratedFiles, System.Data.ParameterDirection.Input));
				}
			}

			return sBuilder.ToString();
		}

		/// <summary>
		/// Specialized list for DAL template: builds entity assignment list when a datarow is the source of the data
		/// </summary>
		/// <returns></returns>
		private static string GetEntityRefreshWithDataRow(Table objTable, bool bExcludeTempFields)
		{
			StringBuilder sBuilder = new StringBuilder();

			for (int x = 0; x < objTable.Columns.Count; x++)
			{
				if (!cgu.CommonUtil.ColumnExcludeCheck(objTable.Columns[x].Name, bExcludeTempFields))
				{
					if (!objTable.Columns[x].IsInPrimaryKey)
					{
						sBuilder.Append(GetDataRowToEntityPropertyAssignment(objTable.Columns[x]));
					}
				}
			}

			return sBuilder.ToString();
		}

		/// <summary>
		/// Specialized list for DAL template: builds entity assignment list when a datareader is the source of the data
		/// </summary>
		/// <returns></returns>
		private static string GetEntityRefreshWithReader(Table objTable, bool bExcludeTempFields)
		{
			StringBuilder sAssignBuilder = new StringBuilder("//Assign reader values to entity");
			StringBuilder sValidateBuilder = new StringBuilder();
			int iColNumber = 0;
			int iTotalColumns = objTable.Columns.Count - GetExcludedColumnCount(objTable, bExcludeTempFields);

			for (int x = 0; x < objTable.Columns.Count; x++)
			{
				if (!cgu.CommonUtil.ColumnExcludeCheck(objTable.Columns[x].Name, bExcludeTempFields))
				{
					//build validation string
					iColNumber++;
					sValidateBuilder.AppendFormat("{0}.EntityHelper.ValidateColumnOrdinal(reader, {1}_Base.{1}Column.{2}, \"{2}\");\r\n", _LibraryNameSpace, _ClassName, cgu.CommonUtil.GetColumnPropertyName(objTable.Columns[x]));
					sValidateBuilder.Append('\t', (iColNumber == iTotalColumns) ? 3 : 4);

					//build entity value assignments
					sAssignBuilder.AppendFormat("\r\n\t\t\tiColNbr = (int){0}_Base.{0}Column.{1};\r\n\t\t\t", _ClassName, cgu.CommonUtil.GetColumnPropertyName(objTable.Columns[x]));
					sAssignBuilder.Append(GetDataReaderToEntityPropertyAssignment(objTable.Columns[x]));
				}
			}

			return "if (bValidateOrdinals)\r\n\t\t\t{\r\n\t\t\t\t" +
									sValidateBuilder.ToString() +
							"}\r\n\r\n\t\t\t" +
							sAssignBuilder.ToString();
		}


		/// <summary>
		/// Called by GetEntityRefreshWithDataRow
		/// </summary>
		/// <param name="objColumn"></param>
		/// <returns></returns>
		private static string GetDataRowToEntityPropertyAssignment(Column objColumn)
		{
			return string.Format("entity.{0} = (Convert.IsDBNull(dataRow[\"{1}\"]))?{2}:({3})dataRow[\"{1}\"];\r\n\t\t\t",
												cgu.CommonUtil.GetColumnPropertyName(objColumn),
												objColumn.Name,
												cgu.CommonUtil.GetColumnIsDBNullValue(objColumn),
												cgu.CommonUtil.GetNullableColumnTypeDef(objColumn));
		}


		/// <summary>
		/// Called by GetEntityRefreshWithReader
		/// </summary>
		/// <param name="objColumn"></param>
		/// <returns></returns>
		private static string GetDataReaderToEntityPropertyAssignment(Column objColumn)
		{
			if (objColumn.IsNullable)
			{
				return string.Format("entity.{0} = (reader.IsDBNull(iColNbr))? null : ({1})reader[iColNbr];\r\n",
														 cgu.CommonUtil.GetColumnPropertyName(objColumn),
														 cgu.CommonUtil.GetNullableColumnTypeDef(objColumn));

			}

			return string.Format("entity.{0} = ({1})reader[iColNbr];\r\n",
														 cgu.CommonUtil.GetColumnPropertyName(objColumn),
														 cgu.CommonUtil.GetNullableColumnTypeDef(objColumn));
		}


		/// <summary>
		/// Fetches the number of columns being excluded from the list
		/// </summary>
		/// <param name="objTable"></param>
		/// <param name="bExcludeTempFields"></param>
		/// <returns></returns>
		private static int GetExcludedColumnCount(Table objTable, bool bExcludeTempFields)
		{
			//fetch number of columns ending with 'temp'
			int iExcludeColumnCount = 0;
			for (int x = 0; x < objTable.Columns.Count; x++)
			{
				if (cgu.CommonUtil.ColumnExcludeCheck(objTable.Columns[x].Name, bExcludeTempFields))
				{
					iExcludeColumnCount++;
				}
			}

			return iExcludeColumnCount;
		}

		// ************************************************************************************
		#endregion table-related private 'helper' procs
		// ************************************************************************************
	}
}